Public Class frmCountry

    Dim dtCities As New DataTable()

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        'boilerplate to populate the dropdown list
        Dim dt As New DataTable()
        Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=MEGACITIES.MDB"
        Dim sqlStr As String = "SELECT DISTINCT Country FROM Countries"
        Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)
        dataAdapter.Fill(dt)
        dataAdapter.Dispose()

        'copy the entries into a dropdown
        For i = 0 To dt.Rows.Count - 1
            cbCountries.Items.Add(dt(i)("Country"))
        Next
    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles btnSaveCities.Click

        Dim strCountryPicked As String = cbCountries.Items(cbCountries.SelectedIndex)
        Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=MEGACITIES.MDB"
        Dim sqlStr As String = "SELECT city, pop2005, pop2015 FROM Cities WHERE Country = '" & strCountryPicked & "'"

        Dim changes As Integer
        Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)
        Dim commandBuilder As New OleDb.OleDbCommandBuilder(dataAdapter)

        commandBuilder.GetUpdateCommand()
        changes = dataAdapter.Update(dtCities)
        dataAdapter.Dispose()
        If changes > 0 Then
            MessageBox.Show(changes & " changed rows.")
        Else
            MessageBox.Show("No changes made.")
        End If


    End Sub

    Private Sub cbCountries_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cbCountries.SelectedIndexChanged
        Dim strCountryPicked As String = cbCountries.Items(cbCountries.SelectedIndex)

        'load the cities into a datagridview
        dtCities.Clear()
        Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=MEGACITIES.MDB"
        Dim sqlStr As String = "SELECT city, pop2005, pop2015 FROM Cities WHERE Country = '" & strCountryPicked & "'"
        Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)

        dataAdapter.Fill(dtCities)
        dataAdapter.Dispose()

        dgvData.DataSource = dtCities

    End Sub



End Class
